﻿using OddUserManager.Data.IRepositories;
using OddUserManager.Model;
using System;
using System.Collections.Generic;
using System.Text;
using Dapper;
using System.Linq;
namespace OddUserManager.Data.Repositories
{
    public class RealTimeJCMatchRepository : RealTimeMacthDapperBaseRepository<JCMatch, int>, IRealTimeJCMatchRepository
    {
        public List<string> GetAllIssues()
        {
            List<string> list = null;
            string sql = "select distinct Issue from JCMatch order by Issue desc";
            using (var con = GetOpenConnection())
            {
                list = con.Query<string>(sql).ToList();
            }
            return list;
        }

        public List<JCMatch> GetMatchsByIssue(string issue)
        {
            List<JCMatch> list = null;
            using (var con = GetOpenConnection())
            {
                var sql = string.Format(@"set nocount on
                                                declare @t TABLE(MatchId int,Issue  nvarchar(100), FiveHundredMatchId int, JCDate datetime,MatchNumber nvarchar(100),TournamanetId int, TournamentName nvarchar(100),HomeTeamName nvarchar(100), AwayTeamName nvarchar(100),MatchTime datetime,WholeScore nvarchar(100));
                                                insert into @t
                                                SELECT m.MatchId,m.Issue,mm.MatchId as FiveHundredMatchId,mm.MatchDate as JCDate, m.MatchNumber,m.TournamanetId,m.TournamentName,m.HomeTeamName,m.AwayTeamName,m.MatchTime,m.WholeScore
                                                  FROM JCMatch m left join MatchMapping mm on m.MatchId=mm.JCMatchId
                                                  where   Issue='{0}';
                                                set nocount off
                                                select mt.*,ms.host_team_id as HomeTeamId,ms.guest_team_id as AwayTeamId,ms.season_id as FiveSeasonId from @t mt left join Matchs ms on mt.FiveHundredMatchId = ms.bet007_id order by mt.MatchTime;
                                                select  c.*,m.CutWinChance as MyCutWinChance,m.CutDrawChance as MyCutDrawChance,m.CutLoseChance as MyCutLoseChance,m.Let1BallsMatchWinChance,m.Let2BallsMatchWinChance,m.Let3BallsMatchWinChance,
                                                m.LetNegative1BallsMatchWinChance,m.LetNegative2BallsMatchWinChance,m.LetNegative3BallsMatchWinChance,m.LetNegative4BallsMatchWinChance,
                                                m.WinPayOut,m.DrawPayOut,m.LosePayOut
                                                 from
												 (select  MatchId,min(SectionNumber) as SectionNumber
                                                 from CutOdds  
												 where MatchId in(select FiveHundredMatchId from @t)
												  group by MatchId) a  
												  left join  CutOdds  c on a.MatchId =c.MatchId and a.SectionNumber =c.SectionNumber
												  left join MyCutOdds m on c.MatchId=m.MatchId and c.SectionNumber=m.SectionNumber", issue);
                //sql = "select * from JCMatch where SellingStatus='Selling'";
                list = con.QueryMultiple(sql, null, null, 3000, null).Map<JCMatch, MergeCutOdds, int>
                      (
                      m => m.FiveHundredMatchId,
                      c => c.MatchId,
                      (m, cs) => { m.CutOddsList = cs.ToList(); }
                      ).ToList();
                var getHadOddSql = string.Format("select * from JCHadOdds where MatchId in (select MatchId from JCMatch where Issue='{0}') union all select * from JCHHadOdds where MatchId in (select MatchId from JCMatch where Issue='{1}')", issue, issue);
                var hadOdds = con.Query<JCHadOdds>(getHadOddSql, null, null, true, 3000, null).ToList();
                for (var i = 0; i < list.ToList().Count; i++)
                {
                    list[i].JCHadOddsList = hadOdds.Where(p => p.MatchId == list[i].MatchId).ToList();
                }
            }
            return list;
        }


        public List<MatchOption> GetMatchOptionByIssues(List<string> issues)
        {
            List<MatchOption> list = null;
            using (var con = GetOpenConnection())
            {
                var issuesString = "'" + string.Join("','", issues) + "'";
                var sql = $"select MatchId as Id,(MatchNumber +' '+HomeTeamName+' VS '+AwayTeamName) as [Text] from JCMatch where Issue in({issuesString});";
                list = con.Query<MatchOption>(sql).ToList();
            }
            return list;
        }


        public List<MatchOption> GetMatchOptionByTime(DateTime startTime, DateTime endTime)
        {
            List<MatchOption> list = null;
            using (var con = GetOpenConnection())
            {

                var sql = $"select MatchId as Id,(MatchNumber +' '+HomeTeamName+' VS '+AwayTeamName) as [Text] from JCMatch where MatchTime>'{startTime.ToString("yyyy-MM-dd HH:mm:ss")}' and MatchTime<'{endTime.ToString("yyyy-MM-dd HH:mm:ss")}';";
                list = con.Query<MatchOption>(sql).ToList();
            }
            return list;
        }

        public List<JCMatch> GetMatchsByIds(List<int> ids)
        {

            List<JCMatch> list = null;
            using (var con = GetOpenConnection())
            {
                var issuesString = string.Join(",", ids);
                var sql = $"select * from JCMatch where  MatchId in({issuesString});" +
                    $"select * from JCHadOdds where MatchId in (select MatchId from JCMatch where MatchId in({issuesString})) union all select * from JCHHadOdds where MatchId in (select MatchId from JCMatch where MatchId in({issuesString}))";
                //sql = "select * from JCMatch where SellingStatus='Selling'";
                list = con.QueryMultiple(sql, null, null, 3000, null).Map<JCMatch, JCHadOdds, int>
                      (
                      m => m.MatchId,
                      c => c.MatchId,
                      (m, cs) => { m.JCHadOddsList = cs.ToList(); }
                      ).ToList();

                if (null != list && list.Count > 0)
                {
                    var hafuOddsSql = $"select * from JCHAFUOdds where MatchId in ({issuesString})";
                    var hafuList = con.Query<JCHAFUOdds>(hafuOddsSql, null, null, true, 3000, null);

                    list.ForEach(p =>
                    {
                        p.JCHAFUOddsList = hafuList.Where(x => x.MatchId == p.MatchId).ToList();
                    });
                }
            }
            return list;
        }

        public List<JCMatch> GetMatchsByIssues(List<string> issues)
        {
            List<JCMatch> list = null;
            using (var con = GetOpenConnection())
            {

                var issuesString = "'" + string.Join("','", issues) + "'";
                var sql = $"select * from JCMatch where  Issue in({issuesString});" +
                    $"select * from JCHadOdds where MatchId in (select MatchId from JCMatch where Issue in({issuesString})) union all select * from JCHHadOdds where MatchId in (select MatchId from JCMatch where Issue in({issuesString}))";
                //sql = "select * from JCMatch where SellingStatus='Selling'";
                list = con.QueryMultiple(sql, null, null, 3000, null).Map<JCMatch, JCHadOdds, int>
                      (
                      m => m.MatchId,
                      c => c.MatchId,
                      (m, cs) => { m.JCHadOddsList = cs.ToList(); }
                      ).ToList();
            }
            return list;
        }

        public List<JCMatch> GetNoFinishedMatch()
        {

            List<JCMatch> list = null;
            using (var con = GetOpenConnection())
            {
                var sql = @"set nocount on
                                                declare @t TABLE(MatchId int,Issue  nvarchar(100), FiveHundredMatchId int, JCDate datetime,MatchNumber nvarchar(100),TournamanetId int, TournamentName nvarchar(100),HomeTeamName nvarchar(100), AwayTeamName nvarchar(100),MatchTime datetime,WholeScore nvarchar(100));
                                                insert into @t
                                                SELECT m.MatchId,m.Issue,mm.MatchId as FiveHundredMatchId,mm.MatchDate as JCDate, m.MatchNumber,m.TournamanetId,m.TournamentName,m.HomeTeamName,m.AwayTeamName,m.MatchTime,m.WholeScore
                                                  FROM JCMatch m left join MatchMapping mm on m.MatchId=mm.JCMatchId
                                                  where   m.SellingStatus='Selling' and DATEDIFF(day,m.MatchTime,GETDATE())<7 and m.MatchTime>GETDATE();
                                                set nocount off
                                                select mt.*,ms.host_team_id as HomeTeamId,ms.guest_team_id as AwayTeamId,ms.season_id as FiveSeasonId from @t mt left join Matchs ms on mt.FiveHundredMatchId = ms.bet007_id order by mt.MatchTime;
                                                select  c.*,m.CutWinChance as MyCutWinChance,m.CutDrawChance as MyCutDrawChance,m.CutLoseChance as MyCutLoseChance,m.Let1BallsMatchWinChance,m.Let2BallsMatchWinChance,m.Let3BallsMatchWinChance,
                                                m.LetNegative1BallsMatchWinChance,m.LetNegative2BallsMatchWinChance,m.LetNegative3BallsMatchWinChance,m.LetNegative4BallsMatchWinChance,
                                                m.WinPayOut,m.DrawPayOut,m.LosePayOut
                                                 from
												 (select  MatchId,min(SectionNumber) as SectionNumber
                                                 from CutOdds  
												 where MatchId in(select FiveHundredMatchId from @t)
												  group by MatchId) a  
												  left join  CutOdds  c on a.MatchId =c.MatchId and a.SectionNumber =c.SectionNumber
												  left join MyCutOdds m on c.MatchId=m.MatchId and c.SectionNumber=m.SectionNumber
                                                ";
                //sql = "select * from JCMatch where SellingStatus='Selling'";
                list = con.QueryMultiple(sql, null, null, 3000, null).Map<JCMatch, MergeCutOdds, int>
                      (
                      m => m.FiveHundredMatchId,
                      c => c.MatchId,
                      (m, cs) => { m.CutOddsList = cs.ToList(); }
                      ).ToList();
                var getHadOddSql = "select * from JCHadOdds where MatchId in (select MatchId from JCMatch where SellingStatus='Selling' and DATEDIFF(day,MatchTime,GETDATE())<7 and MatchTime>GETDATE()) union all select * from JCHHadOdds where MatchId in (select MatchId from JCMatch where SellingStatus='Selling' and DATEDIFF(day,MatchTime,GETDATE())<7 and MatchTime>GETDATE())";
                var hadOdds = con.Query<JCHadOdds>(getHadOddSql, null, null, true, 3000, null).ToList();
                for (var i = 0; i < list.ToList().Count; i++)
                {
                    list[i].JCHadOddsList = hadOdds.Where(p => p.MatchId == list[i].MatchId).ToList();
                }
            }
            return list;
        }

        public JCMatch Get(int id)
        {
            JCMatch match = null;
            using (var con = GetOpenConnection())
            {
                var sql = $"select * from JCMatch where MatchId={id};select * from JCHadOdds where MatchId={id} union all select * from JCHHadOdds where MatchId={id}";
                match = con.QueryMultiple(sql, null, null, 3000, null).Map<JCMatch, JCHadOdds, int>
                      (
                      m => m.MatchId,
                      c => c.MatchId,
                      (m, cs) => { m.JCHadOddsList = cs.ToList(); }
                      ).FirstOrDefault();

                if (null != match)
                {
                    var hafuOddsSql = $"select * from JCHAFUOdds where MatchId={match.MatchId}";
                    match.JCHAFUOddsList = con.Query<JCHAFUOdds>(hafuOddsSql, null, null, true, 3000, null).ToList();
                }
            }
            return match;
        }

        public IEnumerable<JCMatch> GetAllList()
        {
            throw new NotImplementedException();
        }

        public bool InsertOrUpdate(JCMatch entity)
        {
            throw new NotImplementedException();
        }

        public IEnumerable<JCMatch> LoadPageList(int startPage, int pageSize, out int rowCount, Func<JCMatch, bool> where, Func<JCMatch, object> order)
        {
            throw new NotImplementedException();
        }
    }
}
